package lex.dm;

import java.sql.*;
import lex.dm.utils.DBUtils;

public class HelloJavaDB {
	public static void main(String[] args) {
		Connection conn;
		try {
			conn = DBUtils.connect();
			conn.setAutoCommit(false);
			helloDB(conn);
			conn.commit();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	static void addSomeTables(Connection conn) throws SQLException {
		Statement s = conn.createStatement();
		s.execute("create table hellotable1(name varchar(40), score int)");
		s.execute("create table hellotable2(name varchar(40), score int)");
		s.execute("create table hellotable3(name varchar(40), score int)");
	}

	static void helloDB(Connection conn) {
		try {
			conn.setAutoCommit(false);
			// create a table and insert two records
			Statement s = conn.createStatement();
			// s.execute( "create table hellotable(name varchar(40), score int)"
			// );
			// System.out.println( "Created table hellotable" );
			s.execute("insert into hellotable values('Ruth Cao', 86)");
			s.execute("insert into hellotable values ('Flora Shi', 92)");
			s.execute("insert into hellotable values ('liuex', 75)");
			// list the two records
			ResultSet rs = s.executeQuery("SELECT name, score FROM hellotable ORDER BY score");
			System.out.println("name\t\tscore");
			while (rs.next()) {
				StringBuilder builder = new StringBuilder(rs.getString(1));
				builder.append("\t");
				builder.append(rs.getInt(2));
				System.out.println(builder.toString());
			}
			// // delete the table
			// s.execute( "drop table hellotable" );
			// System.out.println( "Dropped table hellotable" );
			rs.close();
			s.close();
			System.out.println("Closed result set and statement");
			conn.commit();
			conn.close();
			System.out.println("Committed transaction and closed connection");
			try { // perform a clean shutdown
				DriverManager.getConnection("jdbc:derby:;shutdown=true");
			} catch (SQLException se) {
				System.out.println("Database shut down normally");
			}
		} catch (Throwable e) {
			e.printStackTrace();
		}
		System.out.println("SimpleApp finished");
	}
}
